﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Configuration;
using System.Data;
using Microsoft.ApplicationBlocks.Data;

/// <summary>
/// Summary description for UNOE
/// </summary>
public class UNOE
{
    private string _conn = ConfigurationManager.ConnectionStrings["UNOE"].ToString();

    /// <summary>
    /// Constructor
    /// </summary>
	public UNOE()
	{		
	}

    /// <summary>
    /// Retorna informacion de órdenes de compra
    /// </summary>
    /// <param name="consecutivoInicial"></param>
    /// <param name="consecutivoFinal"></param>
    /// <returns></returns>
    public DataSet GetOC(int consecutivoInicial, int consecutivoFinal)
    {
        string sql = "SELECT " +
            "f420_fecha fechaorden, f420_id_tipo_docto tipoorden, f420_consec_docto numeroorden, " +
            "f120_referencia referenciaitem, f120_descripcion descitem, f120_id	coditem, " +
            "f421_cant_pedida_base cantidad, f131_id	codigobarra, prov.f200_id codtercero, " +
            "f420_id_sucursal_prov sucproveedor, prov.f200_id codtercero, " +
            "f420_id_moneda_docto monedadocto, f420_id_moneda_conv monedaconv, f420_id_moneda_local monedalocal, " +
            "f150_id	codbodega, f421_id_unidad_medida codunidadmedida, " +
            "convert(varchar(10),f421_fecha_entrega,112) " +
            "fechaentrega, f421_cant_pedida_base cantidad " +
            "from	t420_cm_oc_docto " +
            "inner join t421_cm_oc_movto on f420_rowid = f421_rowid_oc_docto " +
            "inner join t200_mm_terceros prov on prov.f200_rowid = f420_rowid_tercero_prov " +
            "left  join t202_mm_proveedores on f202_rowid_tercero = f420_rowid_tercero_prov and f202_id_sucursal = f420_id_sucursal_prov " +
            "inner join t150_mc_bodegas on f150_rowid = f421_rowid_bodega " +
            "inner join t121_mc_items_extensiones on f421_rowid_item_ext = f121_rowid " +
            "inner join t120_mc_items on f121_rowid_item = f120_rowid " +
            "left outer join " +
            "(select f131_rowid_item_ext f_rowid_item,   max(f131_ts) f_f131_ts " +
            "from	t131_mc_items_barras " +
            "group    by f131_rowid_item_ext ) " +
            "tbl2  on tbl2.f_rowid_item = f421_rowid_item_ext " +
            "left join t131_mc_items_barras on tbl2.f_f131_ts   =  f131_ts " +
            "where  f420_id_clase_docto in (404,405) " +
            "and f420_ind_estado = 1 " +
            "and f420_consec_docto between " + consecutivoInicial.ToString() + " and " + consecutivoFinal.ToString();

        return SqlHelper.ExecuteDataset(this._conn, CommandType.Text, sql);
    }
}